﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data.Common;
using System.Data;

namespace Mind.Data.BasicSelect
{
    enum JoinType
    {
        Inner, Outer
    }

    enum JoinDirection
    {
        Left, Right
    }

    enum ClauseType
    {
        And, Or, Group, Comparison
    }

    class Join
    {
        public JoinDirection Direction { get; set; }
        public JoinType Type { get; set; }
        public Column FromColumn { get; set; }
        public Column ToColumn { get; set; }
    }

    class Table
    {
        public string Alias { get; set; }
        public string Name { get; set; }
    }

    class Column
    {
        public Function Function { get; set; }
        public bool Output { get; set; }
        public Table Table { get; set; }
        public string Name { get; set; }
        public string Alias { get; set; }
        public bool GroupBy { get; set; }

        public Column()
        {
            Function = Function.None;
        }

        internal string GenerateSelect()
        {
            if (Function == Function.None)
                return string.Format("\t{0}.{1} {2}", Table.Alias, Name, Alias);
            else
                return string.Format("\t{3}({0}.{1}) {2}", Table.Alias, Name, Alias, Function.ToString().ToUpper());
        }
    }

    class Clause
    {
        public ClauseType Type { get; set; }

        public Column ComparisonColumn { get; set; }
        public string Comparison { get; set; }
        public object ComparisonValue { get; set; }

        public List<Clause> GroupedClauses { get; private set; }

        public Clause()
        {
            GroupedClauses = new List<Clause>();
        }
    }

    public enum Function
    {
        None,Min,Max,Count,Sum
    }

    public class JoinClause
    {
        private Table FromTable { get; set; }
        private Select Select { get; set; }
        private FromClause From { get; set; }
        private Table ToTable { get; set; }

        internal JoinClause(Select select, string tableName, Table fromTable, FromClause from)
        {
            this.Select = select;
            this.ToTable = new Table()
                {
                    Alias = String.Format("tbl{0:D2}_{1}",  this.Select.Joins.Count+1, tableName),
                    Name = tableName
                };
            this.FromTable = fromTable;
        }

        public JoinClause GetFunction(Function function, string columnName)
        {
            this.Select.GetFunction(this.ToTable, function, columnName);
            return this;
        }

        public JoinClause Get(params string[] columnName)
        {
            this.Select.Get(this.ToTable, columnName);
            return this;
        }

        public JoinClause GroupBy(params string[] columnName)
        {
            this.Select.GroupBy(this.ToTable, columnName);
            return this;
        }

        public JoinClause On(string idColumnName, string fkColumnName)
        {
            return this;
        }
    }


    public class FromClause
    {
        private Select Select { get; set; }
        public FromClause(Select select, string tableName, string alias)
        {
            this.Select = select;
            this.Select.MainTable.Name = tableName;
            this.Select.MainTable.Name = alias;
        }

        public FromClause GetFunction(Function function, string columnName)
        {
            this.Select.GetFunction(this.Select.MainTable, function, columnName);
            return this;
        }

        public FromClause Get(params string[] columnName)
        {
            this.Select.Get(this.Select.MainTable, columnName);
            return this;
        }

        public FromClause GroupBy(params string[] columnName)
        {
            this.Select.GroupBy(this.Select.MainTable, columnName);
            return this;
        }

        public JoinClause LeftOuterJoin(string table, string alias)
        {
            return null;
        }

        public JoinClause LeftInnerJoin(string table, string alias)
        {
            return null;
        }
    }

    public class Select
    {
        internal Dictionary<string, Column> Columns { get; set; }
        internal Table MainTable { get; set; }
        internal Dictionary<string, Join> Joins { get; set; }
        internal Clause Where { get; set; }


        internal void GetFunction(Table table, Function function, params string[] columnName)
        {
            foreach (string columnNameItem in columnName)
            {
                string alias = String.Format("{0}_{1}", table.Alias, columnNameItem);
                Column column = ConfigureColumn(alias, columnNameItem, table);
                column.Function = function;
            }
        }

        internal void Get(Table table, params string[] columnName)
        {
            foreach (string columnNameItem in columnName)
            {
                string alias = String.Format("{0}_{1}", table.Alias, columnNameItem);
                Column column = ConfigureColumn(alias, columnNameItem, table);
                column.Output = true;
            }
        }

        internal void GroupBy(Table table, params string[] columnName)
        {
            foreach (string columnNameItem in columnName)
            {
                string alias = String.Format("{0}_{1}", table.Alias, columnNameItem);
                Column column = ConfigureColumn(alias, columnNameItem, table);
                column.GroupBy = true;
            }
        }


        public Select()
        {
            MainTable = new Table()
            {
                Name = "UndefinedMainTable",
                Alias = "tbl000"
            };

            Columns = new Dictionary<string, Column>();
            Joins = new Dictionary<string, Join>();
        }

        private void BuildWhere(StringBuilder output, Clause clause)
        {
            if (clause.Type == ClauseType.Or || clause.Type == ClauseType.And)
                {
                    bool first = true;
                    foreach (Clause child in clause.GroupedClauses)
                    {
                        if (first) first = false; else output.AppendFormat("{0} ", clause.Type.ToString().ToUpper());
                        BuildWhere(output, child);
                    }
                }

            if (clause.Type == ClauseType.Group)
                {
                    output.AppendLine("(");
                    foreach (Clause child in clause.GroupedClauses)
                        BuildWhere(output, child);
                    output.AppendLine(")");
                }

            if (clause.Type == ClauseType.Comparison)
                {
                    output.AppendFormat("{0}.{1} {2} {3}", clause.ComparisonColumn.Table.Alias, clause.ComparisonColumn.Name, clause.Comparison, clause.ComparisonValue);
                }
            

        }

        public override string ToString()
        {
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT");
            bool first = true;
            foreach (Column col in Columns.Values)
            {
                if (col.Output)
                {
                    if (first) first = false; else select.Append(",");
                    select.AppendLine(col.GenerateSelect());
                    select.AppendLine();
                }
            }

            select.AppendLine("FROM");
            select.AppendFormat("\t{0} AS {1}", MainTable.Name, MainTable.Alias);
            select.AppendLine();

            foreach (Join join in Joins.Values)
            {
                select.AppendFormat("\t{0} {1} JOIN {2} AS {3} ON {3}.{4} = {5}.{6}"
                    , join.Direction.ToString().ToUpper()
                    , join.Type.ToString().ToUpper()
                    , join.ToColumn.Table.Name
                    , join.ToColumn.Table.Alias
                    , join.ToColumn.Name
                    , join.FromColumn.Table.Alias
                    , join.FromColumn.Name);
                select.AppendLine();
            }

            if (Where != null)
            {
                select.AppendLine("WHERE");
                BuildWhere(select, Where);
            }


            return select.ToString();
        }


        public DbDataReader ExecuteReader()
        {
            return DataAccessManager.Current.ExecuteReader(this.ToString());
        }

        public DataTable ExecuteDataTable()
        {
            return DataAccessManager.Current.GetDataTable(this.ToString());
        }

        internal Column ConfigureColumn(string alias, string columnName, Table table)
        {
            if (!Columns.ContainsKey(alias))
            {
                Columns.Add(alias, new Column()
                {
                    GroupBy = false,
                    Name = columnName,
                    Output = false,
                    Table = table,
                    Alias = alias
                });
            }
            return Columns[alias];
        }

        public FromClause From(string tableName)
        {
            return new FromClause(this, tableName, this.MainTable.Alias);
        }

        public FromClause From(string tableName, string alias)
        {
            return new FromClause(this, tableName, alias);
        }

        public void Teste()
        {
            //new Select().From("Table").Get("Id","Poee").Where("ioio").IsEqualsTo("opo").EndFrom().InnerJoin("oooo","aaa").On("id").IsEqualsToColumn("id").InnerJoin()

            //new Select().From("table").LeftInnerJoin("asoas","jkjk").On("asas","asas",1).

            //new Select(")
            //    .Get("Name","OtherTable")
            //    .From("MainTable")
            //    .LeftInnerJoin("MainTable","OtherTable").On("Id").IsEqualsTo("OtherId")
            //    .Where(
            
        }
    }
}
